package com.blb.dao.impl;

import com.blb.dao.CommentDao;
import com.blb.utils.DBUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.blb.pojo.Comment;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class CommentDaoImpl implements CommentDao {

    /**
     * 删除
     * @param id
     * @throws SQLException
     */
    public void delete(String id) throws SQLException {
        Connection conn = DBUtils.getConn();
        DBUtils.executeUpdate(conn,"delete from s_comment where id = ?", id);
        DBUtils.evict(conn);
    }

    @Override
    public void add(Comment comment) throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql = "insert into s_comment(id,topic_id,message,image,user_id) values(?,?,?,?,?)";
        DBUtils.executeUpdate(conn,sql,comment.getId(),comment.getTopicId(),comment.getMessage(),comment.getImage(),comment.getUserId());
        DBUtils.evict(conn);
    }
    @Override

    /**
     * 评论列表
     */
    public List<Comment> list() throws Exception {
        Connection conn = DBUtils.getConn();
        String sql = "select * from s_comment ";
        List<Comment> list = DBUtils.getList(Comment.class, conn, sql);
        DBUtils.evict(conn);
        return list;
    }

    /**
     * 评论总数
     */
    @Override
    public int getCountByCommentId(String id) throws Exception {
        int count=0;
        Connection conn = DBUtils.getConn();
        String sql = "select id,IFNULL(num,0) from s_topic sto\n" +
                "LEFT JOIN (select topic_id,count(topic_id) num from s_comment sc GROUP BY sc.topic_id) a on  sto.id=a.topic_id";
        ResultSet resultSet = DBUtils.query(conn, sql,id);
        if (resultSet.next()){
            count = resultSet.getInt("resultSet");
        }
        DBUtils.evict(conn);
        return count;
    }


}
